package model

import (
	"blog/app/common"
	"blog/app/entity"
	"blog/app/utils"
	"log"
	"time"
)

type Article struct {
	ID       uint64    `json:"id"`
	Title    string    `json:"title"`
	Content  string    `json:"content"`
	CreateAt time.Time `json:"create_at"`
	UpdateAt time.Time `json:"update_at"`
}

func (a *Article) InsertOne() (uint64,bool) {
	log.Println("article model InsertOne")
	now := time.Now()
	sql := "insert into article (title,content,create_at,update_at) value(?,?,?,?)"
	rs, err := common.DB.Exec(sql, a.Title, a.Content, now, now)
	if utils.CheckErr(err) {
		return 0,false
	}
	id, err := rs.LastInsertId()
	if utils.CheckErr(err) {
		return 0,false
	}
	return uint64(id),true
}

func (a *Article) DeleteOne() bool {
	sql := "delete from article where id=?"
	rs, err := common.DB.Exec(sql, a.ID)
	if utils.CheckErr(err) {
		return false
	}
	_, err = rs.RowsAffected()
	if utils.CheckErr(err) {
		return false
	}
	return true
}

func (a *Article) UpdateOne() bool {
	now := time.Now()
	sql := "update article set title=?,content=?,update_at=? where id=?"
	result, err := common.DB.Exec(sql, a.Title, a.Content, now, a.ID)
	if utils.CheckErr(err) {
		return false
	}
	_, err = result.RowsAffected()
	if utils.CheckErr(err) {
		return false
	}
	return true
}

// article 查询一条记录,通过ID
func (a *Article) Get() *entity.Article {
	sql := `SELECT a.id,title,content,DATE_FORMAT(a.create_at,'%Y-%m-%d %T'),DATE_FORMAT(update_at,'%Y-%m-%d %T'),
			COUNT(DISTINCT v.id) ,GROUP_CONCAT(DISTINCT t.name SEPARATOR ';') ,GROUP_CONCAT(DISTINCT t.id SEPARATOR ';')
			FROM article a
			LEFT JOIN article_tag ag ON a.id = ag.article_id
			LEFT JOIN tag t ON t.id = ag.tag_id
			LEFT JOIN visit v ON v.article_id = a.id
			WHERE a.id=?
			GROUP BY a.id`
	article := entity.Article{}
	err := common.DB.QueryRow(sql, a.ID).Scan(&article.ID, &article.Title, &article.Content, &article.CreateDate, &article.UpdateDate, &article.Times, &article.Tags,&article.TagIds)
	if utils.CheckErr(err) {
		return nil
	}
	return &article
}

// article 条件查询总数
func (a *Article) Total(params *entity.QueryParam) uint64 {
	// LEFT(content,200) AS content
	sql := "select count(1) from article where 1=1 "
	var args []interface{}
	if params.CreateDate != "" {
		sql += "and DATE_FORMAT(create_at,'%Y-%m')  = ? "
		args = append(args, params.CreateDate)
	}
	if params.Title != "" {
		sql += "and title like ? "
		args = append(args, "%"+params.Title+"%")
	}
	if len(params.ArticleIds) > 0 {
		sql += "and id in (" + utils.SqlSymbols(len(params.ArticleIds)) + ") "
		args = append(args, params.ArticleIds...)
	}
	var total uint64
	err := common.DB.QueryRow(sql, args...).Scan(&total)
	if utils.CheckErr(err) {
		return 0
	}
	return total
}

// article 条件查询记录
func (a *Article) Query(page *common.Page, params *entity.QueryParam) *[]entity.Article {
	log.Println("article model")
	// LEFT(content,200) AS content
	// 查询文章ID 标题 前200个字内容，创建时间，更新时间，浏览次数，分类集合
	sql := `SELECT a.id,title,LEFT(content,200),DATE_FORMAT(a.create_at,'%Y-%m-%d %T'),DATE_FORMAT(update_at,'%Y-%m-%d %T'),
           COUNT(DISTINCT v.id) ,GROUP_CONCAT(DISTINCT t.name SEPARATOR ';'),
           GROUP_CONCAT(DISTINCT t.id SEPARATOR ';')
			FROM article a
			LEFT JOIN article_tag ag ON a.id = ag.article_id
			LEFT JOIN tag t ON t.id = ag.tag_id
			LEFT JOIN visit v ON v.article_id = a.id
			where 1=1 `
	var args []interface{}
	if params.CreateDate != "" {
		sql += "and DATE_FORMAT(create_at,'%Y-%m')  = ? "
		args = append(args, params.CreateDate)
	}
	if params.Title != "" {
		sql += "and title like ? "
		args = append(args, "%"+params.Title+"%")
	}
	if len(params.ArticleIds) > 0 {
		sql += "and id in (" + utils.SqlSymbols(len(params.ArticleIds)) + ") "
		args = append(args, params.ArticleIds...)
	}
	sql += "GROUP BY a.id limit ? offset ?"
	args = append(args, page.PageSize, page.Offset)
	rows, err := common.DB.Query(sql, args...)
	if utils.CheckErr(err) {
		return nil
	}
	var articles []entity.Article
	for rows.Next() {
		var article entity.Article
		err := rows.Scan(&article.ID, &article.Title, &article.Content, &article.CreateDate, &article.UpdateDate, &article.Times, &article.Tags,&article.TagIds)
		if utils.CheckErr(err) {
			return nil
		}
		articles = append(articles, article)
	}
	rows.Close()
	return &articles
}


// 统计创建 时间 和对应的数量
func (a *Article) GetCreatedateStat() *[]entity.ArticleDate {
	sql := `SELECT DATE_FORMAT(create_at,'%Y-%m') d,COUNT(id) 
           FROM article
           GROUP BY d
           ORDER BY create_at DESC`
	rows, err := common.DB.Query(sql)
	if utils.CheckErr(err) {
		return nil
	}
	var ads []entity.ArticleDate
	for rows.Next() {
		ad := entity.ArticleDate{}
		err := rows.Scan(&ad.CreateDate,&ad.Number)
		if utils.CheckErr(err) {
			return nil
		}
		ads = append(ads, ad)
	}
	rows.Close()
	return &ads
}